# Frierson, Dylan, Lab 5
# 1
USE Books
SELECT COUNT(Title) AS "Amount of Books"
FROM Books
WHERE Category = "Cooking";
# 2
SELECT COUNT(Title) AS "Amount of Books"
FROM Books
WHERE Retail > 30.00;
# 3
SELECT Title, MAX(PubDate)
FROM Books;
# 4 
SELECT Title, MIN(Retail) AS "Lowest Book Price"
FROM Books, OrderItems, Orders, Customers
WHERE Books.ISBN = OrderItems.ISBN AND OrderItems.Ordernum = Orders.Ordernum AND Orders.Customernum = Customers.Customernum
AND Customers.Customernum = 1017;
# 5
SELECT AVG(Retail) AS "Avg Price"
FROM Books
WHERE Category = "Computer";
# 6
SELECT Customernum, COUNT(Ordernum) AS "Number of Orders"
FROM Orders
WHERE Ordernum IS NOT NULL
GROUP BY Customernum
ORDER BY Ordernum, Customernum;
# 7
SELECT Title, Fname, Lname, MAX(Retail) AS "Lisa Most Expensive Books"
FROM Books, BookAuthor, Author
WHERE Books.ISBN = BookAuthor.ISBN AND BookAuthor.AuthorID = Author.AuthorID
AND Fname = "LISA" AND Lname = "WHITE";
# 8
SELECT Customernum, Orders.Ordernum, SUM(Retail * Quantity) AS "Total"
FROM Orders, OrderItems, Books
WHERE Orders.Ordernum = OrderItems.Ordernum AND OrderItems.ISBN = Books.ISBN 
AND Orders.Customernum = 1017;
# 9
SELECT Ordernum, SUM(Retail) AS "Total"
FROM Books, OrderItems
WHERE Books.ISBN = OrderItems.ISBN
GROUP BY Ordernum;
# 10
SELECT DISTINCT CONCAT(FirstName, " ", LastName) AS "Full Name", State
FROM Customers, Orders, OrderItems, Books
WHERE Customers.Customernum = Orders.Customernum AND Orders.Ordernum = OrderItems.Ordernum AND OrderItems.ISBN = Books.ISBN
AND (State = "GA" OR State = "FL")
GROUP BY LastName
HAVING SUM(Retail * quantity) > '80'
ORDER BY LastName;